﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.IO;
using OfficeOpenXml;


namespace Controller.Helper
{
    public class ExcelHelper
    {
        public static List<tblNDPriceDetail> GetExcelDataToNdData(string filePath, int round, bool isFirstRowTitle)
        {
            List<tblNDPriceDetail> ndDetails = new List<tblNDPriceDetail>();
            FileInfo existingFile = new FileInfo(filePath);
            using (ExcelPackage xlPackage = new ExcelPackage(existingFile))
            {

                int row = 1000;
                // get the first worksheet in the workbook
                ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[1];
                //int iCol = 2;  // the column to read

                // output the data in column 2

                for (int iRow = 1; iRow < row; iRow++)
                {
                    var nd = new tblNDPriceDetail();
                    if (isFirstRowTitle && iRow == 1)
                    {
                        iRow += 1;
                    }
                    if (!string.IsNullOrEmpty(worksheet.Cell(iRow, 1).Value))
                    {
                        if (worksheet.Cell(iRow, 1).Value.ToLower().Contains("end"))
                        {
                            break;
                        }
                    }
                    string ndId = worksheet.Cell(iRow, 1).Value;
                    string prodGrop = worksheet.Cell(iRow, 2).Value;
                    string ndprice = worksheet.Cell(iRow, 3).Value;
                    string approveLevel1 = worksheet.Cell(iRow, 4).Value;
                    string approveLevel2 = worksheet.Cell(iRow, 5).Value;
                    string approveLevel3 = worksheet.Cell(iRow, 6).Value;

                    nd.mprodh_grpname = prodGrop;
                    nd.NDRound = round;
                    int indId = -1;
                    decimal dnprice = -1;
                    decimal dapproveLevel1 = -1;
                    decimal dapproveLevel2 = -1;
                    decimal dapproveLevel3 = -1;

                    int.TryParse(ndId, out indId);
                    {
                        if (indId != -1)
                        {
                            nd.mprodh_grpID = indId;
                        }
                    }
                    Decimal.TryParse(ndprice, out dnprice);
                    if (dnprice != -1)
                    {
                        nd.discountPrice = dnprice;
                    }
                    Decimal.TryParse(approveLevel1, out dapproveLevel1);
                    if (dapproveLevel1 != -1)
                    {
                        nd.NDApproverLevel1 = dapproveLevel1;
                    }
                    Decimal.TryParse(approveLevel2, out dapproveLevel2);
                    if (dapproveLevel2 != -1)
                    {
                        nd.NDApproverLevel2 = dapproveLevel2;
                    }
                    Decimal.TryParse(approveLevel3, out dapproveLevel3);
                    if (dapproveLevel3 != -1)
                    {
                        nd.NDApproverLevel3 = dapproveLevel3;
                    }
                    bool duplicatedItem = ndDetails.Any(ndsItem => ndsItem.mprodh_grpname == nd.mprodh_grpname || string.IsNullOrEmpty(nd.mprodh_grpname));
                    if (duplicatedItem != true)
                    {
                        ndDetails.Add(nd);
                    }
                }




            }
            return ndDetails;
        }

        public static List<WaterPipeConvert> GetExcelDataToPipePrice(string filePath, bool isFirstRowTitle)
        {
            var pipes = new List<WaterPipeConvert>();
            var existingFile = new FileInfo(filePath);
            using (var xlPackage = new ExcelPackage(existingFile))
            {

                const int row = 1000;
                // get the first worksheet in the workbook
                ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[1];
                //int iCol = 2;  // the column to read

                // output the data in column 2

                for (int iRow = 1; iRow < row; iRow++)
                {
                    var pipe = new WaterPipeConvert();
                    if (isFirstRowTitle && iRow == 1)
                    {
                        iRow += 1;
                    }
                    if (!string.IsNullOrEmpty(worksheet.Cell(iRow, 1).Value))
                    {
                        if (worksheet.Cell(iRow, 1).Value.ToLower().Contains("end"))
                        {
                            break;
                        }
                    }
                    string BathDisplay = worksheet.Cell(iRow, 1).Value;
                    string ListPrice = worksheet.Cell(iRow, 2).Value;
                    string PercentDisplay15 = worksheet.Cell(iRow, 3).Value;
                    string PercentDisplay30 = worksheet.Cell(iRow, 4).Value;
                    string PercentDisplay60 = worksheet.Cell(iRow, 5).Value;

                    
                    decimal bathDisplay = -1;
                    decimal listPrice = -1;
                    decimal percentDisplay15 = -1;
                    decimal percentDisplay30 = -1;
                    decimal percentDisplay60 = -1;


                    Decimal.TryParse(BathDisplay, out bathDisplay);
                    if (bathDisplay != -1)
                    {
                        pipe.BathDisplay = Math.Round(bathDisplay,2);
                    }
                    Decimal.TryParse(ListPrice, out listPrice);
                    if (listPrice != -1)
                    {
                        pipe.ListPrice = listPrice;
                    }
                    Decimal.TryParse(PercentDisplay15, out percentDisplay15);
                    if (percentDisplay15 != -1)
                    {
                        pipe.PercentDisplay15 = Math.Round(percentDisplay15,2);
                    }
                    Decimal.TryParse(PercentDisplay30, out percentDisplay30);
                    if (percentDisplay30 != -1)
                    {
                        pipe.PercentDisplay30 = Math.Round(percentDisplay30,2);
                    }
                    Decimal.TryParse(PercentDisplay60, out percentDisplay60);
                    if (percentDisplay60 != -1)
                    {
                        pipe.PercentDisplay60 = Math.Round(percentDisplay60,2);
                    }
                   
                        pipes.Add(pipe);
                }
            }
            return pipes;
        }
    }
}
